﻿using RQX.Common.Core.Config;
using RQX.Common.Core.Extension;
using RQX.Common.Core.Logger;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace RQX.Common.Core.Db.Base
{
    /// <summary>
    /// 基础数据库帮助类
    /// </summary>
    public abstract class BaseDbHelper<TDbConnection, TCommand>
        where TDbConnection : DbConnection, new()
        where TCommand : DbCommand, new()
    {
        #region 连接配置
        private static string _connectionStr = "";
        private TDbConnection _conn;
        
        public BaseDbHelper()
        {
            if (_connectionStr.IsNullOrEmpty())
            {
                _connectionStr = GetConnectionStr();
            }
        }
        public BaseDbHelper(string connStr)
        {
            if (_connectionStr.IsNullOrEmpty())
            {
                _connectionStr = connStr;
            }
        }
        /// <summary>
        /// 获取连接字符串
        /// </summary>
        /// <returns></returns>
        protected virtual string GetConnectionStr()
        {
            return ConfigUtils.GetConfigurationRoot().GetValue(GetConfigName());
        }
        /// <summary>
        /// 获取数据库连接的配置节名称
        /// </summary>
        /// <returns></returns>
        public virtual string GetConfigName()
        {
            var name = ConfigUtils.GetConfigurationRoot().GetSection("DataBase").GetValue("CurrentUsedDB");
            if (name.IsNullOrEmpty())
            {
                return null;
            }
            else
            {
                return $"DataBase:{name}";
            }
        }
        /// <summary>
        /// 获取数据库连接
        /// </summary>
        /// <returns></returns>
        protected virtual TDbConnection GetDbConnection()
        {
            if (_conn.IsNull())
            {
                if (_connectionStr.IsNullOrEmpty())
                {
                    throw new Exception("数据库连接配置异常！");
                }
                _conn = CreateDbConnection(_connectionStr);
                _conn.Open();
            }
            if (_conn.State != ConnectionState.Open) _conn.Open();
            return _conn;
        }
        /// <summary>
        /// 创建数据库连接
        /// </summary>
        /// <param name="connectionStr"></param>
        /// <returns></returns>
        protected abstract TDbConnection CreateDbConnection(string connectionStr);
        /// <summary>
        /// 获取cmd
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        protected TCommand GetCommand(string sql) => new TCommand { Connection = GetDbConnection(), CommandText = sql };
        /// <summary>
        /// 获取cmd带参数
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        protected TCommand GetCommand(string sql,DbParameter[] parameters)
        {
            var cmd = new TCommand
            {
                Connection = GetDbConnection(),
                CommandText = sql
            };
            parameters.ForEach(item =>
            {
                cmd.Parameters.Add(item);
            });
            return cmd;
        }

        #endregion

        #region Sql语句执行
        /// <summary>
        /// 执行sql，不进行查询
        /// </summary>
        /// <param name="sql"></param>
        /// <returns>返回受影响的行数</returns>
        public virtual int ExcuteSqlNoQuery(string sql)
        {
            try
            {
                using var cmd = GetCommand(sql);
                return cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 查询单个返回值
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public virtual string ExcuteSingleResult(string sql)
        {
            try
            {
                using var cmd = GetCommand(sql);
                using var reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    return reader[0].ToString();
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 批量执行sql列表,返回成功的条数
        /// </summary>
        /// <param name="sqlList"></param>
        /// <param name="isUseTransaction">是否启用事务</param>
        /// <returns></returns>
        public virtual int ExcuteSqlNoQuery(IEnumerable<string> sqlList, bool isUseTransaction = false)
        {
            try
            {
                using var cmd = GetCommand("");
                int successNum = 0;
                if (isUseTransaction)//启用事务
                {
                    var transaction = GetDbConnection().BeginTransaction();
                    foreach (var sql in sqlList)
                    {
                        try
                        {
                            cmd.CommandText = sql;
                            cmd.ExecuteNonQuery();
                            successNum++;
                        }
                        catch (Exception ex)
                        {
                            transaction.Rollback();
                            throw ex;
                        }
                    }
                    transaction.Commit();
                    return successNum;
                }
                else//不启用事务
                {
                    sqlList.ForEach(sql =>
                    {
                        try
                        {
                            cmd.CommandText = sql;
                            cmd.ExecuteNonQuery();
                            successNum++;
                        }
                        catch (Exception ex)
                        {
                            LogUtils.WriteErrorLog($"数据写入异常！{sql}\n{ex}");
                        }
                    });
                    return successNum;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 执行sql查询单个obj，带func的委托
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="func"></param>
        /// <returns></returns>
        public virtual T ExcuteSingleObj<T>(string sql,Func<IDataReader,T> func)
        {
            try
            {
                using var cmd = GetCommand(sql);
                using var reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    return func(reader);
                }
                return default;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        } 
        /// <summary>
        /// 执行sql查询多个list，带func的委托
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="func"></param>
        /// <returns></returns>
        public virtual IEnumerable<T> ExcuteList<T>(string sql, Func<IDataReader, T> func)
        {
            try
            {
                using var cmd = GetCommand(sql);
                using var reader = cmd.ExecuteReader();
                List<T> resultList = new List<T>();
                while (reader.Read())
                {
                    var item = func(reader);
                    if (item.IsNotNull()) resultList.Add(item);
                }
                return resultList;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion
    }
}
